﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MINERP.DAL.StartOrder
{
    public  class StartOrderDAL
    {
        public  string conStr = DALBase.ConnStr;
         SqlConnection con;

        /// <summary>
        /// 获取系统时间
        /// </summary>
        /// <returns></returns>
        public  DateTime GetServerTime()
        {
            try
            {
                DateTime time = DateTime.Now;
                using (con = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "Proc_GetDate";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = con;
                    con.Open();
                    time = Convert.ToDateTime(cmd.ExecuteScalar());
                }
                return time;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取商户所勾选产品
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public DataTable GetProductShop(int shopId)
        {
            using (con = new SqlConnection(conStr))
            {
                int delStatus = (int)COM.ProductStatus.Delete;
                try
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("select tb_product.ProuctId as ProductId,ProductName,tb_product.Status,UsedTimes,tb_product.Width,tb_product.CoverWidth,tb_product.Height,tb_product.CoverHeight,PageNum,Tb_PorductCategory.ProductCategoryId,ProductCategoryName,");
                    sql.Append(" ProductTypeCode as ProductTypeCode,paperFace.PaperFaceId,paperFace.Name as PaperFaceName,paperType.PaperTypeId,paperType.Name as PaperTypeName,");
                    sql.Append(" mnemonicCode as ZJCode");
                    sql.Append(" from Shop_Product_Price inner join  tb_product on Shop_Product_Price.ProductId=tb_product.ProuctId inner join Tb_PorductCategory on tb_product.ProductCategoryId=Tb_PorductCategory.ProductCategoryId");
                    sql.Append(" inner join tb_ProductType on tb_product.PorductTypeId=tb_ProductType.ProductTypeId");
                    sql.Append(" inner join paperFace on tb_product.paperfaceid=paperFace.paperfaceid");
                    sql.Append(" inner join papertype on tb_product.papertypeid=papertype.papertypeid");
                    sql.Append(" where Shop_Product_Price.shopid=" + shopId + " and tb_product.status!=" + delStatus);
                    SqlCommand cmd = new SqlCommand(sql.ToString(), con);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 获得产品绑定的附加收费
        /// </summary>
        /// <returns></returns>
        public DataTable GetProductCommonChange()
        {
            using (con = new SqlConnection(conStr))
            {
                string strSQL = "select Productid,ProductCommonItem.CommonChargeItemId as CommonChargeItemId,ItemName from ProductCommonItem,CommonChargeItem where CommonChargeItem.CommonChargeItemId=ProductCommonItem.CommonChargeItemId";
                SqlCommand cmd = new SqlCommand(strSQL.ToString(), con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 获取一个产品所有的委外商
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public List<MODEL.CustomerManage.StartOrder.ProductOutShop> GetProductOutShop(int productId)
        {
            try
            {
                List<MODEL.CustomerManage.StartOrder.ProductOutShop> pos = new List<MINERP.MODEL.CustomerManage.StartOrder.ProductOutShop>();
                using (con = new SqlConnection(conStr))
                {
                    string sql = "select Tb_OutShop_ProductCategory.OutShopId,ProductId,IsDefaultOutShop,OutShopName from Tb_OutShop_ProductCategory inner join OutProcessShop on Tb_OutShop_ProductCategory.OutShopId=OutProcessShop.OutShopId where ProductId=" + productId;
                    SqlCommand cmd = new SqlCommand(sql, con);
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        MODEL.CustomerManage.StartOrder.ProductOutShop po = new MINERP.MODEL.CustomerManage.StartOrder.ProductOutShop();
                        po.IsDefaultOutShop = Convert.ToBoolean(reader["IsDefaultOutShop"]);
                        po.OutShopId = Convert.ToInt32(reader["OutShopId"]);
                        po.ProductId = Convert.ToInt32(reader["ProductId"]);
                        po.OutShopName = Convert.ToString(reader["OutShopName"]);
                        pos.Add(po);
                    }
                    reader.Dispose();
                }
                return pos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
